-- @owner: @kyeleze
-- @date: 2022/6/9
-- @testpoint: ceil函数结合join条件

--step1:建表 expect:建表成功
drop table if exists t_ustore_ceil_0024_1;
drop table if exists t_ustore_ceil_0024_2;
create table t_ustore_ceil_0024_1(
col_1 decimal,
col_2 int ,
col_3 float8,
col_4 decimal(6,2)
) with (storage_type=ustore);
create table t_ustore_ceil_0024_2(col_ceil decimal) with (storage_type=ustore);
 
--step2:创建序列 expect:序列创建成功
drop sequence if exists seq_ustore_ceil_0024;
create sequence seq_ustore_ceil_0024 increment by 1 start with 100000;
 
--step3:循环插入1000条数据 expect:插入成功
begin
 for i in 1..1000 loop
      insert into t_ustore_ceil_0024_1 values(
   3.1415926+seq_ustore_ceil_0024.nextval,
   i,
   1.0e+100,
   i+445.255
   );
    end loop;
end;
/

--step4:插入数据 expect:数据插入成功
insert into t_ustore_ceil_0024_2 values (100816.1415926);
insert into t_ustore_ceil_0024_2 values (100884.1415926);

--step5:结合ceil函数查询数据 expect:结果为100816.1415926、100884.1415926
select col_ceil from t_ustore_ceil_0024_2 inner join t_ustore_ceil_0024_1 on ceil(col_ceil) = ceil(col_1) order by col_ceil;
select col_ceil from t_ustore_ceil_0024_2 left join t_ustore_ceil_0024_1 on ceil(col_ceil) = ceil(col_1)  order by col_ceil;

--step6:清理环境 expect:清理成功
drop table if exists t_ustore_ceil_0024_1;
drop table if exists t_ustore_ceil_0024_2;
drop sequence if exists seq_ustore_ceil_0024;